package cn.iune.erp.accounting;


import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.NoResultException;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.justep.baas.Utils;
import com.justep.baas.data.DataUtils;
import com.justep.baas.data.Table;
import com.justep.baas.data.Transform;

import cn.iune.common.base.BaseJPAImpl;


@Repository
public class SheetBalanceDaoJPAImpl extends BaseJPAImpl<SheetBalance> implements ISheetBalanceDao {

	private String kSql;
	
//	@PersistenceContext 
//	private EntityManager em ;


	@Override
	public void insert(SheetBalance obj) {
		em.persist(obj);
	}

	@Override
	public void delete(String fID) {
//		SheetBalance obj = em.find(SheetBalance.class, fID);
		em.remove(em.getReference(SheetBalance.class, fID));
	}

	@Override
	public SheetBalance update(SheetBalance obj) {
		SheetBalance objNew = em.merge(obj);
		return objNew;
	}

	@Override
	public SheetBalance getByID(String fID) {
		SheetBalance singleResult = em.find(SheetBalance.class, fID);
		return singleResult;
	}

	@Override
	public List<SheetBalance> getAll() {
		kSql="SELECT SheetBalance FROM SheetBalance SheetBalance ";
		Query query = em.createQuery(kSql);
		@SuppressWarnings("unchecked")
		List<SheetBalance> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public SheetBalance getByNo(String sheetCode, String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT SheetBalance FROM SheetBalance SheetBalance "
				+ " WHERE ( SheetBalance.sheetCode = :sheetCode ) "
				+ " AND ( SheetBalance.enterpriseNo = :enterpriseNo ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("sheetCode", sheetCode);
		query.setParameter("enterpriseNo", enterpriseNo);
		SheetBalance singleResult = null;
		try{
			singleResult = (SheetBalance) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
		}finally{
			
		}
		return singleResult;
	}
	
	@Override
	public JSONObject create(JSONObject data) {
		//缺省列
		String defColstr = Utils.getDefCols(SheetBalance.class);
		data.put("defColstr", defColstr);
		return Utils.newData(data);

	}
	
	@Override
	public JSONObject query(JSONObject data) throws SQLException {
		
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String fParent = data.getString("fParent");

		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
		String select = "SheetBalance.*"
//				+ ", CONCAT(SheetBalance.sheetName,'[',SheetBalance.sheetCode,']') as sDescribe "
				+ ", CONCAT('[',SheetBalance.sheetCode,']',SheetBalance.sheetName) as sDescribe "

//				+ ",PeriodCaption.debitPeriod"
//				+ ",PeriodCaption.creditPeriod"
//				+ ",PeriodCaption.dob"
//				+ ",PeriodCaption.dobLabel"
				+ ",IFNULL(PeriodCaption.balanceYearBegin,0) AS balanceYearBegin"
				+ ",IFNULL(PeriodCaption.balanceEnd,0) AS balanceEnd"
				+ ",IFNULL(PeriodCaption.balanceYearEnd,0) AS balanceYearEnd"
				
				//计算借贷余额（下面的两种方案都可以正常使用）
				//+ ",IFNULL((PeriodCaption.debitPeriod-PeriodCaption.creditPeriod)*PeriodCaption.dob,0) AS balancePeriod"
				//+ ",CASE PeriodCaption.dob WHEN 1 THEN (PeriodCaption.debitPeriod-PeriodCaption.creditPeriod) WHEN -1 THEN (PeriodCaption.creditPeriod-PeriodCaption.debitPeriod) ELSE 0 END AS balancePeriod"
				//+ ",CASE PeriodCaption.dob WHEN 1 THEN 9999 WHEN -1 THEN -9999 ELSE 0 END AS balancePeriod"
				
				;
				
				String from = "SheetBalance SheetBalance"
				+ " LEFT JOIN PeriodCaption PeriodCaption ON (PeriodCaption.captionNo=SheetBalance.sheetCode AND PeriodCaption.enterpriseNo=SheetBalance.enterpriseNo)"
				;
		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("SheetBalance.sheetFullCode LIKE ? OR SheetBalance.sheetFullName LIKE ? OR SheetBalance.comments LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 3; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("fID='"+id+"'");
		}
		//处理fParent
		if (Utils.isNotEmptyString(fParent)) {
			filters.add("fParent='"+fParent+"'");
		}
		
		//处理条件过滤
		JSONArray filterList = data.getJSONArray("filters"); // 列定义
		if(filterList!=null && filterList.size()>0){
			for(int i=0;i<filterList.size();i++){
				JSONObject jsonFilter = filterList.getJSONObject(i);
//				System.out.println("jsonFilter.getString(\"filter\")-->"+jsonFilter.getString("filter"));
				filters.add(jsonFilter.getString("filter"));
			}
		}
		
		String where = "";
	
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
		//获取最大深度
//		String sqlLevel = String.format(format, "MAX(SheetBalance.nodeLevel)", from, where, "");
//		Object level = DataUtils.getValueBySQL(conn, sqlLevel, sqlParams);
//		int maxLevel = Integer.parseInt(level.toString());
//		System.out.println("最大深度maxLevel-->>"+maxLevel);

//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);
		//关闭连接
		conn.close();

		table.setTotal(Integer.parseInt(total.toString()));
		table.setOffset(offset);
		table.setTableName("PeriodCaption");
		JSONObject json = Transform.tableToJson(table);
		
//		json.getJSONObject("userdata").put("maxLevel", maxLevel);
		
		return json;
	}

	//资产负债表显示（查询后，汇总上一级）
	@Override
	public JSONObject shown(JSONObject data) throws SQLException {
		
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String fParent = data.getString("fParent");

		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
		String select = "SheetBalance.*"
//				+ ", CONCAT(SheetBalance.sheetName,'[',SheetBalance.sheetCode,']') as sDescribe "
				+ ", CONCAT('[',SheetBalance.sheetCode,']',SheetBalance.sheetName) as sDescribe "

//				+ ",PeriodCaption.debitPeriod"
//				+ ",PeriodCaption.creditPeriod"
//				+ ",PeriodCaption.dob"
//				+ ",PeriodCaption.dobLabel"
				+ ",IFNULL(PeriodCaption.balanceYearBegin,0) AS balanceYearBegin"
				+ ",IFNULL(PeriodCaption.balanceEnd,0) AS balanceEnd"
				+ ",IFNULL(PeriodCaption.balanceYearEnd,0) AS balanceYearEnd"
				
				//计算借贷余额（下面的两种方案都可以正常使用）
				//+ ",IFNULL((PeriodCaption.debitPeriod-PeriodCaption.creditPeriod)*PeriodCaption.dob,0) AS balancePeriod"
				//+ ",CASE PeriodCaption.dob WHEN 1 THEN (PeriodCaption.debitPeriod-PeriodCaption.creditPeriod) WHEN -1 THEN (PeriodCaption.creditPeriod-PeriodCaption.debitPeriod) ELSE 0 END AS balancePeriod"
				//+ ",CASE PeriodCaption.dob WHEN 1 THEN 9999 WHEN -1 THEN -9999 ELSE 0 END AS balancePeriod"
				
				;
		
		String from = "SheetBalance SheetBalance"
				+ " LEFT JOIN PeriodCaption PeriodCaption ON (PeriodCaption.captionNo=SheetBalance.sheetCode AND PeriodCaption.enterpriseNo=SheetBalance.enterpriseNo)"
				;
		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("SheetBalance.sheetFullCode LIKE ? OR SheetBalance.sheetFullName LIKE ? OR SheetBalance.comments LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 3; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("fID='"+id+"'");
		}
		//处理fParent
		if (Utils.isNotEmptyString(fParent)) {
			filters.add("fParent='"+fParent+"'");
		}
		
		//处理条件过滤
		JSONArray filterList = data.getJSONArray("filters"); // 列定义
		if(filterList!=null && filterList.size()>0){
			for(int i=0;i<filterList.size();i++){
				JSONObject jsonFilter = filterList.getJSONObject(i);
//				System.out.println("jsonFilter.getString(\"filter\")-->"+jsonFilter.getString("filter"));
				filters.add(jsonFilter.getString("filter"));
			}
		}
		
		String where = "";
	
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
		//获取最大深度
//		String sqlLevel = String.format(format, "MAX(SheetBalance.nodeLevel)", from, where, "");
//		Object level = DataUtils.getValueBySQL(conn, sqlLevel, sqlParams);
//		int maxLevel = Integer.parseInt(level.toString());
//		System.out.println("最大深度maxLevel-->>"+maxLevel);

//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);
		//关闭连接
		conn.close();

		table.setTotal(Integer.parseInt(total.toString()));
		table.setOffset(offset);
		table.setTableName("PeriodCaption");
		
		/*
		///////////////////////////////////////////////////////////
		System.out.println("开始计算......");
		List<Row> rows = table.getRows();
		//逐级计算
		for(int i=maxLevel-1;i>0;i--) {
			for(Row row:rows) {
				int nodeLevel = row.getInteger("nodeLevel");
				String nodeKind = row.getString("nodeKind");
				if(nodeLevel==i && nodeKind==null) {
					//不计算末端（叶子），因为末端是总账数据
					System.out.println("row.sheetFullName-->>"+row.getString("sheetFullName"));
					BigDecimal balancePeriod = BigDecimal.ZERO;
					//汇总
					String fID = row.getString("fID");
					for(Row item:rows) {
						if(fID.equals(item.getString("fParent"))) {
							System.out.println("item.sheetFullName-->>"+item.getString("sheetFullName"));
							balancePeriod = balancePeriod.add(item.getDecimal("balancePeriod"));
						}
					}
					//赋值（无法实现赋值，转换成JSON赋值，再转回去好像没有什么意思）
					row.
				}
			}
			
			System.out.println("++++++++++++++++++++++i-->>"+i);
		}
		
		System.out.println("计算完成");
		///////////////////////////////////////////////////////////
		*/

		JSONObject json = Transform.tableToJson(table);
		
//		json.getJSONObject("userdata").put("maxLevel", maxLevel);
		
		return json;
	}

	@Override
	public int getRootCount() {
		// TODO Auto-generated method stub
		kSql="SELECT COUNT(SheetBalance) as count " 
				+ " FROM SheetBalance SheetBalance " 
				+ " WHERE ( SheetBalance.fParent is null ) "
				;
		Query query = em.createQuery(kSql);
		
		long singleResult =  (long) query.getSingleResult();
		return (int) singleResult;
	}

	@Override
	public List<SheetBalance> getRoots(String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT SheetBalance FROM SheetBalance SheetBalance "
				+ " WHERE ( SheetBalance.fParent is null ) "
				+ " AND ( SheetBalance.enterpriseNo = :enterpriseNo ) "
				+ " ORDER BY SheetBalance.sortNo ASC "
				;
		
		Query query = em.createQuery(kSql);
		query.setParameter("enterpriseNo", enterpriseNo);
		
		@SuppressWarnings("unchecked")
		List<SheetBalance> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public int getChildCount(String fParent) {
		// TODO Auto-generated method stub
		kSql= "SELECT COUNT(SheetBalance) as count " 
				+ " FROM SheetBalance SheetBalance " 
				+ " WHERE ( SheetBalance.fParent = :fParent ) "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("fParent", fParent);
		
		long singleResult =  (long) query.getSingleResult();
		return (int) singleResult;
	}

	@Override
	public List<SheetBalance> getChilds(String fParent) {
		// TODO Auto-generated method stub
		kSql="SELECT SheetBalance FROM SheetBalance SheetBalance "
				+ " WHERE ( SheetBalance.fParent =:fParent ) "
				+ " ORDER BY SheetBalance.sortNo ASC "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("fParent", fParent);
		
		@SuppressWarnings("unchecked")
		List<SheetBalance> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public List<SheetBalance> getChildren(String fParent) {
		// TODO Auto-generated method stub
		kSql="SELECT SheetBalance FROM SheetBalance SheetBalance "
//				+ " WHERE ( SheetBalance.storageFullID like '%:fParent%' ) "
				+ " WHERE ( SheetBalance.sheetFullID like '%" + fParent + "%' ) "
				+ " AND ( SheetBalance.fID <> '" + fParent + "' ) "
				+ " ORDER BY SheetBalance.sortNo DESC"
				;
		Query query = em.createQuery(kSql);
//		query.setParameter("fParent", fParent);
		@SuppressWarnings("unchecked")
		List<SheetBalance> resultList = query.getResultList();
		return resultList;
	}


}
